package com.wyl.search.sql;

import java.util.HashMap;
import java.util.Map;
import java.util.Stack;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.http.HttpServletRequest;

/**
 * @author SUNK.XU
 *
 *
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name']
[FROM table_references
[WHERE where_definition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_definition]
[ORDER BY {col_name | expr | position}
[ASC | DESC] , ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]

 *
 */
public class FormationSql {
    private static String HEAD_CHARACTER="$[";
    private static String TAIL_CHARACTER="]";
    private static String BRACKET_SQL="BRACKETSQL";
    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
		/*String sql1 = "select * from table1 t1 where t1.c1='$[v1]' and t1.c2='$[v2]' and t1.c3=(select * from table2 t2 where t2.c1='$[v3]' and t2.c2='$[v4]' and t2.c3=(select * from table3 t3 where t3.c1='$[v5]' and t3.c2='$[v6]'))";
		String sql2 = "select * from table1 t1 where t1.c1='$[v1]' and t1.c2='$[v2]' and 1=1";*/
        String[] sql={
        		"select account.id id,account.first_name firstName,account.last_name lastName,account.country_id countryId,country.country_name countryName from member_account account left join info_country country on account.country_id=country.id where account.first_name='$[FIRST_NAME]'",
                "-------------------	normal",
                "select * from table1 t1 where C1 IN (SELECT * FROM TABLE WHERE t1.c1 like '$[C1]') and (t1.c2 like '%$[v2]%') and (1=1)",
                "select * from table1 t1 left join table2 t2 on t1.c1=t2.c1 where t1.c2=$[v5] and t2.c2=$[v6]",

                "-------------------	order by having",
                "select * from table1 t1 where t1.c1=$[V8] order by c2",
                "select * from table1 t1 where t1.cl = (select t2.c1 from t2 where t2.c1=$[V8] order by c2)",
                "select * from table1 t1 where t1.cl = (select t2.c1 from t2 where t2.c1=$[V8] order by c2) and t1.c2='$[v2]'",

                "-------------------	group by having",
                "select * from table1 t1 where t1.c1=$[V8] group by t1.c1 having max(c1)>$[V5] order by c2",

                "select c1 from table3 t3 where t2.c1='$[V3]' and t3.c2='$[v6]' group by t3.c3 having max(t3.c3)<$[V4] order by t3.c3",

                "select * from table2 t2 where t2.c1='$[v3]' and t2.c2='$[v4]' and " +
                        "t2.c3 in (select c1 from table3 t3 where t2.c1='$[V3]' and t3.c2='$[v6]' group by t3.c3 having max(t3.c3)<$[V4] order by t3.c3",

                "select * from table1 t1 where t1.c1='$[v1]' or t1.c2='$[v2]' and " +
                        "t1.c3=(select * from table2 t2 where t2.c1='$[v3]' and t2.c2='$[v4]' and " +
                        "t2.c3 in (select c1 from table3 t3 where t2.c1='$[V6]' and t3.c2='$[v6]' group by t3.c3 having max(t3.c3)<$[V7] order by t3.c3))"
        };
        Map params = new HashMap();
        params.put("V1", "c1");
        params.put("V2", "c2");
        params.put("V3", "c3");
        params.put("V4", "c4");
        params.put("FIRST_NAME", "FIRST_NAME");        
        long startTime = System.currentTimeMillis();
        for(String sqlElement:sql)
            System.out.println(buildSql(sqlElement,params));
        System.out.println(System.currentTimeMillis()-startTime);
      

    }
    /**
     * Http Request Map
     * @param sql
     * @param request
     * @return
     */
    public static String buildSql(String sql,HttpServletRequest request) {
        Map<String,String> bracketSql = new HashMap<String,String>();
        Stack<Integer> stack = new Stack<Integer>();
        StringBuffer sqlBuffer = new StringBuffer();
        String tempSql =null;
        String result=null;

        char[] sqlArray = sql.toCharArray();
        for(int i=0;i<sqlArray.length;i++){
            sqlBuffer.append(sqlArray[i]);
            if(sqlArray[i]=='('){
                stack.push(sqlBuffer.length()-1);
            }else if(sqlArray[i]==')'){
                int leftBracketPosition = stack.pop();
                tempSql= sqlBuffer.substring(leftBracketPosition+1,sqlBuffer.length()-1);
                sqlBuffer.delete(leftBracketPosition,sqlBuffer.length());
                tempSql = fillValue(tempSql,request,false);
                if(tempSql!=null && !"".equals(tempSql)){
                    sqlBuffer.append("("+BRACKET_SQL+i+")");

                    bracketSql.put(BRACKET_SQL+i, tempSql);
                }else{
                    sqlBuffer.append("("+"$["+BRACKET_SQL+"]"+")");
                }
            }
        }
        tempSql = fillValue(sqlBuffer.toString(),request,true);
        result = fillBracketSql(tempSql,bracketSql);
        return result;
    }



    public static String buildSql(String sql,Map<String,String> params) {
        Map<String,String> bracketSql = new HashMap<String,String>();
        Stack<Integer> stack = new Stack<Integer>();
        StringBuffer sqlBuffer = new StringBuffer();
        String tempSql =null;
        String result=null;

        char[] sqlArray = sql.toCharArray();
        for(int i=0;i<sqlArray.length;i++){
            sqlBuffer.append(sqlArray[i]);
            if(sqlArray[i]=='('){
                stack.push(sqlBuffer.length()-1);
            }else if(sqlArray[i]==')'){
                int leftBracketPosition = stack.pop();
                tempSql= sqlBuffer.substring(leftBracketPosition+1,sqlBuffer.length()-1);
                sqlBuffer.delete(leftBracketPosition,sqlBuffer.length());
                tempSql = fillValue(tempSql,params,false);
                if(tempSql!=null && !"".equals(tempSql)){
                    sqlBuffer.append("("+BRACKET_SQL+i+")");

                    bracketSql.put(BRACKET_SQL+i, tempSql);
                }else{
                    sqlBuffer.append("("+"$["+BRACKET_SQL+"]"+")");
                }
            }
        }
        tempSql = fillValue(sqlBuffer.toString(),params,true);
        result = fillBracketSql(tempSql,bracketSql);
        return result;
    }

    /**
     *
     * @param sql
     * @param bracketSql
     * @return
     */
    public static String fillBracketSql(String sql,Map<String,String> bracketSql){
        String regex=BRACKET_SQL+"\\d+";
        Pattern pattern=Pattern.compile(regex);
        Matcher matcher=pattern.matcher(sql);
        if(matcher.find()){
            String temp=sql.substring(matcher.start(),matcher.end());
            sql=sql.replaceAll(temp, bracketSql.get(temp));
            sql=fillBracketSql(sql,bracketSql);
        }

        return sql;
    }

    /**
     * Http Request Map
     * @param sql
     * @param request
     * @param isTopLevel
     * @return
     */
    public static String fillValue(String sql, HttpServletRequest request, boolean isTopLevel) {
        sql = sql.toUpperCase();
        while (sql.indexOf(HEAD_CHARACTER) > 0) {
            int start = sql.indexOf(HEAD_CHARACTER);
            int end = sql.indexOf(TAIL_CHARACTER);
            String variableName = sql.substring(start + 2, end);
            if (request.getParameter(variableName) != null && !"".equals(request.getParameter(variableName))) {
                sql = sql.replace(sql.substring(start, end + 1),request.getParameter(variableName));
            } else {
                sql = alterSql(sql,start,end+1,isTopLevel);
            }
        }
        return sql;
    }

    /**
     *
     * @param sql
     * @param params
     * @return
     */
    public static String fillValue(String sql, Map<String,String> params,boolean isTopLevel) {
        sql = sql.toUpperCase();
        while (sql.indexOf(HEAD_CHARACTER) > 0) {
            int start = sql.indexOf(HEAD_CHARACTER);
            int end = sql.indexOf(TAIL_CHARACTER);
            String variableName = sql.substring(start + 2, end);
            if (params.get(variableName) != null) {
                sql = sql.replace(sql.substring(start, end + 1),params.get(variableName));
            } else {
                sql = alterSql(sql,start,end+1,isTopLevel);
            }
        }
        return sql;
    }

    /**
     *
     * @param sql
     * @param start
     * @param end
     * @return
     */
    public static String alterSql(String sql,int start,int end,boolean isTopLevel) {
        String headSql=sql.substring(0,start);
        String tailSql=sql.substring(end);

        int headPosition=0,tailPosition=tailSql.length();
        String headWord=null,tailWord=null,actualWord=null;
        //ȡSQL���ǰ��εĹؼ���
        if(headSql.lastIndexOf(" WHERE ")>headPosition){
            headPosition=headSql.lastIndexOf(" WHERE ");
            headWord=" WHERE ";
        }
        if(headSql.lastIndexOf(" AND ")>headPosition){
            headPosition=headSql.lastIndexOf(" AND ");
            headWord=" AND ";
        }
        if(headSql.lastIndexOf(" OR ")>headPosition){
            headPosition=headSql.lastIndexOf(" OR ");
            headWord=" OR ";
        }
        if(headSql.lastIndexOf(" HAVING ")>headPosition){
            headPosition=headSql.lastIndexOf(" HAVING ");
            headWord=" HAVING ";
        }
        headSql = headSql.substring(0,headPosition);
        //ȡSQL�����εĹؼ���
        if(tailSql.indexOf(" AND ")>=0 && tailSql.indexOf(" AND ")<tailPosition){
            tailPosition=tailSql.indexOf(" AND ");
            tailWord=" AND ";
        }
        if(tailSql.indexOf(" OR ")>=0 && tailSql.indexOf(" OR ")<tailPosition){
            tailPosition=tailSql.indexOf(" OR ");
            tailWord=" OR ";
        }
        if(tailSql.indexOf(" GROUP BY ")>=0 && tailSql.indexOf(" GROUP BY ")<tailPosition){
            tailPosition=tailSql.indexOf(" GROUP BY ");
            tailWord=" GROUP BY ";
        }
        if(tailSql.indexOf(" ORDER BY ")>=0 && tailSql.indexOf(" ORDER BY ")<tailPosition){
            tailPosition=tailSql.indexOf(" ORDER BY ");
            tailWord=" ORDER BY ";
        }

        if(tailPosition==tailSql.length())
            tailWord="";

        actualWord=actualWord(headWord,tailWord);
		
		/*��� order by �� group by */

        if(" -- ".equals(actualWord) && !isTopLevel){
            tailSql=" WHERE ";
        }else if(" -- ".equals(actualWord) && isTopLevel){
            actualWord="";
            tailSql = tailSql.substring(tailPosition);
        }else
            tailSql = actualWord + tailSql.substring(tailPosition+tailWord.length());


        String result = headSql + tailSql;

        if(!isTopLevel)
            result = alterSqlWhere(result);
        else
            result = topAlterSqlWhere(result);
        return result;
    }


    /**
     *
     * @param headWord
     * @param tailWord
     * @return
     */
    public static String actualWord(String headWord,String tailWord){
        String conWord=headWord+tailWord;
        /**
         * where, and, or, order by,  group by
         */
        String regEx[][]={
                {" WHERE  OR "," WHERE "},
                {" WHERE  AND "," WHERE "},
                {" WHERE "," WHERE "},
                {" WHERE  ORDER BY "," -- "},
                {" WHERE  GROUP BY "," GROUP BY "},

                {" AND  AND "," AND "},
                {" AND  OR "," OR "},
                {" AND ",""},
                {" AND  ORDER BY "," ORDER BY "},
                {" AND  GROUP BY "," GROUP BY "},

                {" OR  AND"," OR "},
                {" OR  OR "," OR "},
                {" OR ",""},
                {" OR  ORDER BY "," ORDER BY "},
                {" OR  GROUP BY "," GROUP BY "},

                {" HAVING  AND"," HAVING "},
                {" HAVING  OR "," OR "},
                {" HAVING ",""},
                {" HAVING  ORDER BY "," ORDER BY "}
        };

        for(int i=0;i<regEx.length;i++){
            if(regEx[i][0].equalsIgnoreCase(conWord))
                return regEx[i][1];
        }
        return conWord;
    }
    /**
     *
     * @param sql
     * @return
     */
    public static String alterSqlWhere(String sql){
        String trimSql=sql.trim();
        if(trimSql.length()-5==trimSql.lastIndexOf("WHERE"))
            return "";
        return sql;
    }

    /**
     *
     * @param sql
     * @return
     */
    public static String topAlterSqlWhere(String sql){
        String trimSql=sql.trim();
        if(trimSql.length()-5==trimSql.lastIndexOf("WHERE"))
            return sql.substring(0,sql.indexOf("WHERE")).trim();
        int i=-1;
        if((i=sql.indexOf(" WHERE  ORDER BY "))>0)
            return sql.substring(0,i).trim()+sql.substring(i+7);
        return sql;
    }






}